import pool from "@/app/database";
import { format, ResultSetHeader, RowDataPacket } from "mysql2";

export async function addHandle<T extends Record<string, any>>(
  obj: T,
  tableName: string
) {
  const connection = await pool.getConnection();
  await connection.beginTransaction(); // 开启事务
  try {
    const addFields: string[] = [];
    const values: any[] = [];
    Object.entries(obj).map(([key, value]) => {
      if (
        key !== "creat_time" &&
        key !== "update_time" &&
        key !== "id" &&
        key !== "menuIds" &&
        key !== "role_id"
      ) {
        addFields.push(key);
        if (typeof value === "string") {
          values.push(value || "");
        } else {
          values.push(value || 0);
        }
      }
    });
    addFields.push("version");
    values.push(0);
    console.log(addFields, values);
    const statement = `
    INSERT INTO ${tableName}
      (${addFields.join(", ")})
    VALUES (${values.map(() => "?").join(", ")})
  `;
    const [result] = await connection.execute<ResultSetHeader>(
      statement,
      values
    );
    if (!result.insertId) {
      throw new Error("添加失败");
    }
    if (obj?.menuIds && obj.menuIds.length > 0) {
      // 分离处理
      const map = new Map<string, number>();
      let menu_ids: any = [];
      obj.menuIds.forEach((item: any) => {
        if (item.includes("-")) {
          let temp = item.split("-");
          let num = map.get(temp[0]);
          if (!num) {
            num = 0;
          }
          num |= parseInt(temp[1]);
          map.set(temp[0], num);
        } else {
          menu_ids.push([item, 0]);
        }
      });
      menu_ids = menu_ids.map((item: any) => {
        return [item[0], map.get(item[0]) || 0];
      });
      const values = menu_ids.map((menuId: number[]) => [
        result.insertId,
        ...menuId,
      ]);
      const sql = format(
        "INSERT INTO role_menu (role_id, menu_id , oper) VALUES ?",
        [values]
      );
      const [result_menu] = await connection.execute<ResultSetHeader>(sql);
      if (!result_menu.insertId) {
        throw new Error("添加失败(role_menu)");
      }
    }
    if (obj?.role_id && obj.role_id) {
      const [result_role] = await connection.execute<ResultSetHeader>(
        `INSERT INTO user_role
        (user_id , role_id)
        VALUES (? , ?)
        `,
        [result.insertId, obj.role_id]
      );
      if (!result_role.insertId) {
        throw new Error("添加失败（user_role）");
      }
    }
    await connection.commit(); // 提交事务
    return {
      id: result.insertId,
      createdAt: new Date().toISOString(),
    };
  } catch (error: any) {
    await connection.rollback(); // 回滚事务
    throw new Error(error || "添加失败");
  } finally {
    connection.release();
  }
}

export async function updateHandle<T extends Record<string, any>>(
  obj: T,
  tableName: string
) {
  console.log(obj);
  const connection = await pool.getConnection();
  await connection.beginTransaction(); // 开启事务
  try {
    const updateFields: string[] = [];
    const values: any[] = [];
    // 验证版本号（乐观锁）
    const [verfiy] = await connection.execute<RowDataPacket[]>(
      `SELECT version FROM ${tableName} WHERE id = ?`,
      [obj.id]
    );
    if (!verfiy[0]) {
      throw new Error("记录不存在，更新失败");
    }
    if (verfiy[0].version !== obj.version) {
      throw new Error("数据已被修改，请重试");
    }
    // 处理更新字段（排除关联字段）
    Object.entries(obj).map(([key, value]) => {
      if (
        value !== null &&
        key !== "create_time" &&
        key !== "update_time" &&
        key !== "id" &&
        key !== "menuIds" &&
        key !== "role_id" &&
        key !== "user_id"
      ) {
        updateFields.push(`${key} = ?`);
        if (key === "version") {
          values.push(value + 1);
        } else {
          values.push(value);
        }
      }
    });
    // 主表更新SQL
    const statement = `
      UPDATE ${tableName}
      SET ${updateFields.join(", ")}
      WHERE id = ? AND version = ?
    `;
    values.push(obj.id);
    values.push(obj.version);
    const [result] = await connection.execute<ResultSetHeader>(
      statement,
      values
    );
    if (result.affectedRows === 0) {
      throw new Error("主表更新失败");
    }
    // 处理角色-菜单关联（先删后增，确保关联关系最新）
    if (obj?.menuIds && obj.menuIds.length > 0) {
      // 先删除旧关联
      await connection.execute(`DELETE FROM role_menu WHERE role_id = ?`, [
        obj.id,
      ]);
      // 分离处理
      const map = new Map<string, number>();
      let menu_ids: any = [];
      obj.menuIds.forEach((item: any) => {
        if (item.includes("-")) {
          let temp = item.split("-");
          let num = map.get(temp[0]);
          if (!num) {
            num = 0;
          }
          num |= parseInt(temp[1]);
          map.set(temp[0], num);
        } else {
          menu_ids.push([item, 0]);
        }
      });
      menu_ids = menu_ids.map((item: any) => {
        return [item[0], map.get(item[0]) || 0];
      });
      // 再插入新关联
      const menuValues = menu_ids.map((menuId: number[]) => [
        obj.id,
        ...menuId,
      ]);
      const menuSql = format(
        "INSERT INTO role_menu (role_id, menu_id , oper) VALUES ?",
        [menuValues]
      );
      const [menuResult] = await connection.execute<ResultSetHeader>(menuSql);
      if (menuResult.affectedRows !== menu_ids.length) {
        throw new Error("角色-菜单关联更新不完整");
      }
    }
    // 处理用户-角色关联
    if (obj?.role_id) {
      // 先删除旧关联
      await connection.execute(`DELETE FROM user_role WHERE user_id = ?`, [
        obj.id,
      ]);
      // 再插入新关联
      const [roleResult] = await connection.execute<ResultSetHeader>(
        `INSERT INTO user_role (user_id, role_id) VALUES (?, ?)`,
        [obj.id, obj.role_id]
      );
      if (!roleResult.insertId) {
        throw new Error("用户-角色关联更新失败");
      }
    }
    await connection.commit(); // 提交事务
    return result.affectedRows > 0;
  } catch (error: any) {
    await connection.rollback(); // 回滚事务
    throw new Error(error || "更新失败");
  } finally {
    connection.release();
  }
}
export async function deleteHandle(ids: number[], tableName: string) {
  const connection = await pool.getConnection();
  await connection.beginTransaction(); // 开启事务
  try {
    // 删除关联表数据（根据主表类型判断关联表）
    if (tableName === "role") {
      // 若删除角色，同步删除角色-菜单关联
      const rolePlaceholders = ids.map(() => "?").join(", ");
      await connection.execute(
        `DELETE FROM role_menu WHERE role_id IN (${rolePlaceholders})`,
        ids
      );
    } else if (tableName === "user") {
      // 若删除用户，同步删除用户-角色关联
      const userPlaceholders = ids.map(() => "?").join(", ");
      await connection.execute(
        `DELETE FROM user_role WHERE user_id IN (${userPlaceholders})`,
        ids
      );
    }
    // 删除主表数据
    const placeholders = ids.map(() => "?").join(", ");
    const statement = `
      DELETE FROM ${tableName} WHERE id IN (${placeholders})
    `;
    const [result] = await connection.execute<ResultSetHeader>(statement, ids);
    if (result.affectedRows === 0) {
      throw new Error("主表数据删除失败");
    }
    // 查询剩余总数
    const [totalRes] = await connection.execute<RowDataPacket[]>(
      `SELECT COUNT(*) as total FROM ${tableName}`
    );
    await connection.commit(); // 提交事务
    return totalRes[0].total >= 0;
  } catch (error: any) {
    await connection.rollback(); // 回滚事务
    throw new Error(error || "删除失败");
  } finally {
    connection.release();
  }
}

const TABLE_FIELDS: Record<string, string[]> = {
  user: ["id", "username", "email", "phone", "avatar", "status"],
  role: ["id", "role_name", "role_code", "description", "status"],
  menu: [
    "id",
    "parent_id",
    "menu_name",
    "menu_type",
    "path",
    "component",
    "perms",
    "icon",
    "sort",
    "status",
    "create_time",
    "update_time",
    "version",
  ],
  oper_log: [
    "id",
    "title",
    "business_type",
    "method",
    "request_method",
    "oper_name",
    "oper_url",
    "oper_ip",
    "oper_location",
    "oper_param",
    "json_result",
    "status",
    "error_msg",
    "oper_time",
  ],
};

export async function listHandle<
  T extends Record<string, any>,
  R extends Record<string, any>
>(obj: T, tableName: string): Promise<API.ResponseApi<R>> {
  const connection = await pool.getConnection();
  try {
    const { current = 1, pageSize = 10 } = obj;
    if (tableName === "auth") {
      // 验证必须参数
      if (!obj.id) throw new Error("缺少用户ID参数");

      const [menuRows] = await connection.execute<RowDataPacket[]>(
        `
        SELECT DISTINCT menu.* ,role_menu.oper
        FROM user
        LEFT JOIN user_role ON user.id = user_role.user_id
        LEFT JOIN role ON user_role.role_id = role.id AND role.status = 1
        LEFT JOIN role_menu ON role.id = role_menu.role_id
        LEFT JOIN menu ON role_menu.menu_id = menu.id AND menu.status = 1
        WHERE user.id = ?
        ORDER BY menu.sort ASC
      `,
        [obj.id]
      );

      return {
        list: menuRows as R[],
        current: 1,
        pageSize: menuRows.length,
        total: menuRows.length,
      };
    }

    // where条件处理
    const allowFields = TABLE_FIELDS[tableName] || [];
    const conditions: string[] = [];
    const values: any[] = [];
    // 提取查询限制
    Object.entries(obj).forEach(([key, value]) => {
      if (
        key !== "current" &&
        key !== "pageSize" &&
        value !== undefined &&
        allowFields.includes(key)
      ) {
        if (typeof value === "string") {
          conditions.push(`${tableName}.${key} LIKE ?`);
          values.push(`%${value}%`);
        } else {
          conditions.push(`${tableName}.${key} = ?`);
          values.push(value);
        }
      }
    });
    const whereClause = conditions.length
      ? `WHERE ${conditions.join(" AND ")}`
      : "";
    const primaryKey = `${tableName}.id`;

    // 1. 查询主键分页
    const offset = Number(current - 1) * Number(pageSize);
    const pageSizeNum = Number(pageSize);
    const idStatement = `
      SELECT ${primaryKey} as pid FROM ${tableName}
      ${whereClause}
      ORDER BY ${primaryKey} DESC
      LIMIT ${offset}, ${pageSizeNum};
    `;
    // 只传 where 参数
    const [idRows] = await connection.execute<RowDataPacket[]>(
      idStatement,
      values
    );
    const ids = (idRows as RowDataPacket[]).map((row) => row.pid);

    // 2. 总数
    const countStatement = `
      SELECT COUNT(*) as total FROM ${tableName}
      ${whereClause};
    `;
    // console.log("[listHandle] countStatement:", countStatement);
    // console.log("[listHandle] countParams:", values);

    const countQuestionCount = (countStatement.match(/\?/g) || []).length;
    if (countQuestionCount !== values.length) {
      console.error(
        `[listHandle] 总数参数个数与SQL不符，应为${countQuestionCount}，实际${values.length}`
      );
      throw new Error(
        `总数参数个数与SQL不符，应为${countQuestionCount}，实际${values.length}`
      );
    }
    const [countRows] = await connection.execute<RowDataPacket[]>(
      countStatement,
      values
    );
    // 没查到该 id 的数据
    if (!ids.length) {
      return { list: [], current, pageSize, total: countRows[0]?.total ?? 0 };
    }

    // 3. 多表查询
    let selectFields = `${tableName}.*`;
    let joinClause = "";
    if (tableName === "role") {
      selectFields = `
        ${tableName}.id as role_id,
        ${tableName}.role_name,
        ${tableName}.role_code,
        ${tableName}.description,
        ${tableName}.status,
        ${tableName}.version,
        ${tableName}.create_time as create_time,
        ${tableName}.update_time as update_time,
        role_menu.oper as menu_child,
        menu.id as menu_id,
        menu.menu_name as menu_name,
        menu.path as menu_path,
        menu.menu_type as menu_type
      `;
      joinClause = `
        LEFT JOIN role_menu ON ${tableName}.id = role_menu.role_id
        LEFT JOIN menu ON role_menu.menu_id = menu.id
      `;
    } else if (tableName === "user") {
      selectFields = `
        ${tableName}.id as user_id,
        ${tableName}.username,
        ${tableName}.password,
        ${tableName}.email,
        ${tableName}.phone,
        ${tableName}.avatar,
        ${tableName}.status,
        ${tableName}.version,
        ${tableName}.create_time as create_time,
        ${tableName}.update_time as update_time,
        role.id as role_id,
        role.role_name as role_name,
        role.role_code as role_code
      `;
      joinClause = `
        LEFT JOIN user_role ON ${tableName}.id = user_role.user_id
        LEFT JOIN role ON user_role.role_id = role.id
      `;
    }

    const inClause = ids.map(() => "?").join(",");
    const listStatement = `
      SELECT ${selectFields}
      FROM ${tableName}
      ${joinClause}
      WHERE ${primaryKey} IN (${inClause})
      ORDER BY ${primaryKey} DESC
    `;
    // 错误检测打印
    // console.log("[listHandle] listStatement:", listStatement);
    // console.log("[listHandle] listParams:", ids);

    const listQuestionCount = (listStatement.match(/\?/g) || []).length;
    if (listQuestionCount !== ids.length) {
      console.error(
        `[listHandle] 多表查询参数个数与SQL不符，应为${listQuestionCount}，实际${ids.length}`
      );
      throw new Error(
        `多表查询参数个数与SQL不符，应为${listQuestionCount}，实际${ids.length}`
      );
    }
    const [listRows] = await connection.execute<RowDataPacket[]>(
      listStatement,
      ids
    );
    // 4. 格式化
    const formattedList = formatAssociatedData(
      listRows as RowDataPacket[],
      tableName
    );

    // 5. 返回
    return {
      list: formattedList as R[],
      current,
      pageSize,
      total: countRows[0]?.total ?? 0,
    };
  } catch (error: any) {
    // 错误详细打印
    console.error("[listHandle] 查询失败:", error);
    throw new Error(`查询失败: ${error.message}` || "查询失败");
  } finally {
    connection.release();
  }
}

function formatAssociatedData(rows: any[], tableName: string) {
  if (!rows.length) return [];
  if (tableName === "role" || tableName === "user") {
    const resultMap = new Map<string, any>();
    for (const row of rows) {
      // 1. 确定主键值（role_id/user_id）和目标主键字段（id）
      const primaryKey = tableName === "role" ? "role_id" : "user_id";
      const primaryId = row[primaryKey]; // 原始主键值（如14）
      if (!primaryId) continue; // 过滤无效主键

      // 2. 初始化主记录（将 role_id/user_id 映射为 id）
      if (!resultMap.has(primaryId)) {
        const baseRecord: Record<string, any> = {};
        Object.entries(row).forEach(([key, value]) => {
          // 映射主键：role_id → id；user_id → id
          if (key === primaryKey) {
            baseRecord["id"] = value; // 核心：统一主键为 id
          }
          // 保留非关联字段（排除 menu_*/role_* 前缀的字段）
          else if (
            (tableName === "role" && !key.startsWith("menu_")) ||
            (tableName === "user" && !key.startsWith("role_"))
          ) {
            baseRecord[key] = value;
          }
        });
        // 初始化关联字段（去重）
        if (tableName === "role") baseRecord.menuIds = new Set<string>(); // 用 Set 去重
        if (tableName === "user") baseRecord.roles = new Set<string>();
        resultMap.set(primaryId, baseRecord);
      }

      // 3. 处理关联数据（去重 + 过滤 null）
      const mainRecord = resultMap.get(primaryId);
      if (tableName === "role" && row.menu_id) {
        mainRecord.menuIds.add(row.menu_id.toString()); // 用 Set 避免重复
        for (let i = 0; i < 4; ++i) {
          if (row.menu_child & (1 << i)) {
            mainRecord.menuIds.add(
              row.menu_id.toString() + "-" + (1 << i).toString()
            );
          }
        }
      } else if (tableName === "user" && row.role_id) {
        mainRecord.roles.add(row.role_id);
      }
    }

    // 4. 将 Set 转为数组（前端更易处理）
    return Array.from(resultMap.values()).map((record) => ({
      ...record,
      ...(tableName === "role" && { menuIds: Array.from(record.menuIds) }),
      ...(tableName === "user" && { roles: Array.from(record.roles) }),
    }));
  }
  return rows;
}
